﻿using System;
using System.Data.SQLite;

class Program
{
    static string connectionString = "Data Source=school.db";

    static void Main()
    {
        InitDatabase();

        while (true)
        {
            Console.WriteLine("\n学生管理系统菜单：");
            Console.WriteLine("1. 添加学校");
            Console.WriteLine("2. 添加班级");
            Console.WriteLine("3. 添加学生");
            Console.WriteLine("4. 查看所有学生");
            Console.WriteLine("5. 修改学生信息");
            Console.WriteLine("6. 删除学生");
            Console.WriteLine("7. 查看操作日志");
            Console.WriteLine("0. 退出");
            Console.Write("请选择操作：");

            string choice = Console.ReadLine();

            switch (choice)
            {
                case "1": AddSchool(); break;
                case "2": AddClass(); break;
                case "3": AddStudent(); break;
                case "4": ViewStudents(); break;
                case "5": UpdateStudent(); break;
                case "6": DeleteStudent(); break;
                case "7": ViewLogs(); break;
                case "0": return;
                default: Console.WriteLine("无效选择。"); break;
            }
        }
    }

    static void InitDatabase()
    {
        using var conn = new SQLiteConnection(connectionString);
        conn.Open();
        var cmd = conn.CreateCommand();

        cmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS School (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT
            );
            CREATE TABLE IF NOT EXISTS Class (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT,
                SchoolId INTEGER,
                FOREIGN KEY (SchoolId) REFERENCES School(Id)
            );
            CREATE TABLE IF NOT EXISTS Student (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT,
                Age INTEGER,
                ClassId INTEGER,
                FOREIGN KEY (ClassId) REFERENCES Class(Id)
            );
            CREATE TABLE IF NOT EXISTS Log (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Time TEXT,
                Action TEXT,
                TableName TEXT,
                Detail TEXT
            );
        ";
        cmd.ExecuteNonQuery();
    }

    static void AddSchool()
    {
        Console.Write("输入学校名称：");
        string name = Console.ReadLine();
        Execute($"INSERT INTO School (Name) VALUES ('{name}')");
        InsertLog("INSERT", "School", $"Name={name}");
    }

    static void AddClass()
    {
        Console.Write("输入班级名称：");
        string name = Console.ReadLine();
        Console.Write("输入所属学校ID：");
        string schoolId = Console.ReadLine();
        Execute($"INSERT INTO Class (Name, SchoolId) VALUES ('{name}', {schoolId})");
        InsertLog("INSERT", "Class", $"Name={name}, SchoolId={schoolId}");
    }

    static void AddStudent()
    {
        Console.Write("输入学生姓名：");
        string name = Console.ReadLine();
        Console.Write("输入年龄：");
        string age = Console.ReadLine();
        Console.Write("输入班级ID：");
        string classId = Console.ReadLine();
        Execute($"INSERT INTO Student (Name, Age, ClassId) VALUES ('{name}', {age}, {classId})");
        InsertLog("INSERT", "Student", $"Name={name}, Age={age}, ClassId={classId}");
    }

    static void ViewStudents()
    {
        Console.WriteLine("\n所有学生信息：");
        using var conn = new SQLiteConnection(connectionString);
        conn.Open();
        var cmd = new SQLiteCommand("SELECT Id, Name, Age, ClassId FROM Student", conn);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine($"ID:{reader["Id"]} 姓名:{reader["Name"]} 年龄:{reader["Age"]} 班级ID:{reader["ClassId"]}");
        }
    }

    static void UpdateStudent()
    {
        Console.Write("输入要修改的学生ID：");
        string id = Console.ReadLine();
        Console.Write("新姓名：");
        string name = Console.ReadLine();
        Console.Write("新年龄：");
        string age = Console.ReadLine();
        Execute($"UPDATE Student SET Name='{name}', Age={age} WHERE Id={id}");
        InsertLog("UPDATE", "Student", $"Id={id}, Name={name}, Age={age}");
    }

    static void DeleteStudent()
    {
        Console.Write("输入要删除的学生ID：");
        string id = Console.ReadLine();
        Execute($"DELETE FROM Student WHERE Id={id}");
        InsertLog("DELETE", "Student", $"Id={id}");
    }

    static void ViewLogs()
    {
        Console.WriteLine("\n日志记录：");
        using var conn = new SQLiteConnection(connectionString);
        conn.Open();
        var cmd = new SQLiteCommand("SELECT Time, Action, TableName, Detail FROM Log ORDER BY Time DESC", conn);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine($"[{reader["Time"]}] {reader["Action"]} on {reader["TableName"]} : {reader["Detail"]}");
        }
    }

    static void Execute(string sql)
    {
        using var conn = new SQLiteConnection(connectionString);
        conn.Open();
        var cmd = new SQLiteCommand(sql, conn);
        cmd.ExecuteNonQuery();
    }

    static void InsertLog(string action, string table, string detail)
    {
        string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string sql = $"INSERT INTO Log (Time, Action, TableName, Detail) VALUES ('{time}', '{action}', '{table}', '{detail}')";
        Execute(sql);
    }
}
